/*
 修正后的分区绘图数据表
 作者: Wu.Liang
 日期: 2025-01-30
 说明: 移除zone_id字段，因为一个社区保存一个绘图数据，包含多个分区和楼栋
*/

-- ----------------------------
-- Table structure for sc_zone_drawing (修正版)
-- ----------------------------
DROP TABLE IF EXISTS `sc_zone_drawing`;
CREATE TABLE `sc_zone_drawing` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '绘图数据ID',
  `community_id` bigint(20) NOT NULL COMMENT '社区ID',
  `property_company_id` bigint(20) NOT NULL COMMENT '物业公司ID',
  `drawing_data` longtext NOT NULL COMMENT '绘图数据(JSON格式，包含所有分区和楼栋信息)',
  `canvas_width` int(11) DEFAULT '800' COMMENT '画布宽度',
  `canvas_height` int(11) DEFAULT '600' COMMENT '画布高度',
  `zoom_level` decimal(3,2) DEFAULT '1.00' COMMENT '缩放级别',
  `version` int(11) DEFAULT '1' COMMENT '版本号',
  `is_current` tinyint(1) DEFAULT '1' COMMENT '是否为当前版本：1-是，0-否',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user_id` bigint(20) DEFAULT NULL COMMENT '创建用户ID',
  `update_user_id` bigint(20) DEFAULT NULL COMMENT '更新用户ID',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：1-已删除，0-未删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_community_current` (`community_id`,`is_current`,`deleted`),
  KEY `idx_community_id` (`community_id`),
  KEY `idx_property_company_id` (`property_company_id`),
  KEY `idx_is_current` (`is_current`),
  KEY `idx_version` (`version`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_zone_drawing_community_property_current` (`community_id`,`property_company_id`,`is_current`,`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='社区分区绘图数据表';

-- ----------------------------
-- Records of sc_zone_drawing (示例数据)
-- ----------------------------
BEGIN;
INSERT INTO `sc_zone_drawing` (`id`, `community_id`, `property_company_id`, `drawing_data`, `canvas_width`, `canvas_height`, `zoom_level`, `version`, `is_current`, `create_time`, `update_time`, `create_user_id`, `update_user_id`, `deleted`) VALUES 
(1, 1, 1, '{
  "version": "1.0",
  "canvas": {
    "width": 800,
    "height": 600,
    "zoomLevel": 1.0
  },
  "zones": [
    {
      "id": 1,
      "name": "东区",
      "color": "#409EFF",
      "points": "50,50 250,50 250,250 50,250",
      "textX": 150,
      "textY": 150,
      "buildings": [1,2,3,4]
    },
    {
      "id": 2,
      "name": "南区", 
      "color": "#67C23A",
      "points": "50,300 250,300 250,500 50,500",
      "textX": 150,
      "textY": 400,
      "buildings": [5,6,7,8]
    }
  ],
  "buildings": [
    {
      "id": 1,
      "name": "1幢",
      "type": "residential",
      "x": 100,
      "y": 100,
      "zoneId": 1
    },
    {
      "id": 2,
      "name": "2幢",
      "type": "residential", 
      "x": 200,
      "y": 100,
      "zoneId": 1
    }
  ],
  "metadata": {
    "lastModified": "2025-01-30T10:30:00Z",
    "modifiedBy": "user123",
    "communityId": 1,
    "propertyCompanyId": 1
  }
}', 800, 600, 1.00, 1, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0);
COMMIT;

-- ----------------------------
-- 数据迁移脚本 (从旧表迁移到新表)
-- ----------------------------

/*
-- 迁移步骤：
-- 1. 备份原表数据
-- 2. 创建新表结构
-- 3. 迁移数据（按社区ID分组，每个社区保留最新的绘图数据）
-- 4. 验证数据完整性
-- 5. 删除旧表

-- 备份原表
CREATE TABLE sc_zone_drawing_backup AS SELECT * FROM sc_zone_drawing;

-- 迁移数据（每个社区保留最新的绘图数据）
INSERT INTO sc_zone_drawing_new (
  community_id, property_company_id, drawing_data, 
  canvas_width, canvas_height, zoom_level, version, is_current,
  create_time, update_time, create_user_id, update_user_id, deleted
)
SELECT 
  community_id,
  property_company_id,
  drawing_data,
  canvas_width,
  canvas_height,
  zoom_level,
  version,
  is_current,
  create_time,
  update_time,
  create_user_id,
  update_user_id,
  deleted
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY community_id ORDER BY update_time DESC) as rn
  FROM sc_zone_drawing_backup
  WHERE deleted = 0
) t
WHERE t.rn = 1;
*/
